"""
Case Type   : 分区表锁降级
Case Name   : alter drop分区,1秒后跨分区select数据
Create At   : 2021/10/13
Owner       : z00562814
Description :
    1、创建范围分区表;
    2、向分区1、分区2、分区3中插入数据;
    3、session1中删除分区1;
    4、1秒后session2中在分区表中select数据;
    5、清理环境;
Expect      :
    1、创建范围分区表成功;
    2、插入数据成功;
    3、session1中删除分区1成功;
    4、session2中在分区表中select数据成功，未阻塞;
    5、清理环境成功;
History     :
    因机器性能问题用例执行时间不一致会偶现失败，
    优化用例，减少分区数据，控制用例执行时长，不影响功能验证
    优化用例，修改用例名及表名信息
    优化用例,修改等待时间,添加查询时间,方便定位问题
"""

import os
import unittest
import time
from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.CommonSQL import CommonSQL
from testcase.utils.Logger import Logger


class LockLevel(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} 开始执行-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.comSQL = CommonSQL('PrimaryDbUser')
        self.table_name = 't_locklevel_0046'
        self.i_function_name = 'f_insert_locklevel_0046'

    def test_lock_level(self):
        self.log.info('-----创建插入数据函数-----')
        func_var = 'pvar'
        func_sql1 = f'''insert into {self.table_name} values({func_var},1);'''
        insert_func = self.comSQL.create_func(self.i_function_name,
                                              execute_sql=func_sql1,
                                              var=func_var,
                                              start=20000,
                                              end=80000,
                                              step=20000)
        self.log.info(insert_func)
        self.assertTrue(self.i_function_name in insert_func)

        text = '-----step1:创建范围分区表; expect:成功-----'
        self.log.info(text)
        create_cmd = f'''drop table if exists {self.table_name};
            create table {self.table_name} (id1 int,id2 int) 
            partition by range (id1)
            (partition p00 values less than (20000),
             partition p01 values less than (40000),
             partition p02 values less than (60000));'''
        self.log.info(create_cmd)
        create_msg = self.comSQL.execut_db_sql(create_cmd)
        self.log.info(create_msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, create_msg,
                      '执行失败:' + text)

        text = '-----step2:首先向分区3中插入部分数据; expect:成功-----'
        self.log.info(text)
        sql = f'''select {insert_func}(100);
            select {insert_func}(20100);
            select {insert_func}(40100);
            select count(*) from {self.table_name};'''
        self.log.info(sql)
        msg = self.comsh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertEqual(19, len(msg.splitlines()), "执行失败:" + text)
        self.assertTrue('303' in msg.splitlines()[-2].strip(),
                        '执行失败' + text)

        text = '-----step3:session1中删除分区1; expect:成功-----'
        self.log.info(text)
        add_cmd = f'''select current_time;
            alter table {self.table_name} drop partition p00;
            select relname from pg_partition where parentid = \
            (select oid from pg_class where relname = '{self.table_name}') \
            order by relname;
            select current_time;'''
        session_1 = ComThread(self.comsh.execut_db_sql, args=(add_cmd, ''))
        session_1.setDaemon(True)
        session_1.start()
        time.sleep(1)

        text = '-----step4 & step5:等待1s后在session2中在分区表中select数据; ' \
               'expect:查看数据成功-----'
        self.log.info(text)
        select_cmd = f'''select current_time;
            select * from {self.table_name} limit 20;
            select count(*) from {self.table_name};
            select current_time;'''
        session_2 = ComThread(self.comSQL.execut_db_sql, args=(select_cmd,))
        session_2.setDaemon(True)
        session_2.start()

        self.log.info('-----session2执行结果-----')
        session_2.join(50)
        session_2_res = session_2.get_result()
        self.log.info(session_2_res)

        text = '-----session1执行结果-----'
        self.log.info(text)
        session_1.join(60)
        session_1_res = session_1.get_result()
        self.log.info(session_1_res)
        self.assertEqual(38, len(session_2_res.splitlines()), "执行失败:" + text)
        self.assertTrue('(20 rows)' in session_2_res.splitlines()[-11]
                        and '202' in session_2_res.splitlines()[-7].strip(),
                        "执行失败:" + text)
        self.assertEqual(18, len(session_1_res.splitlines()), "执行失败:" + text)
        self.assertTrue(self.constant.ALTER_TABLE_MSG in session_1_res
                        and 'p00' not in session_1_res.splitlines()[-8],
                        "执行失败:" + text)

    def tearDown(self):
        text = '-----step6:清理环境; expect:成功-----'
        self.log.info(text)
        drop_cmd = f'''drop table {self.table_name} cascade;
            drop function {self.i_function_name};'''
        drop_msg = self.comsh.execut_db_sql(drop_cmd)
        self.log.info(drop_msg)
        self.assertTrue(self.constant.DROP_TABLE_SUCCESS in drop_msg and
                        self.constant.DROP_FUNCTION_SUCCESS_MSG in drop_msg)
        text = f'-----{os.path.basename(__file__)} 执行完成-----'
        self.log.info(text)
